﻿using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Utility;

namespace BLL
{
    public class Sys_ModuleBLL : BaseBLL
    {
        public string OrderByNum(string id)
        {
            string sql = "";

            sql = @"select max(t.module_ordernum1), max(t.module_ordernum2)
  from sys_module t
 where t.module_parentid = " + id + @"
 order by t.module_ordernum1, t.module_ordernum2";

            DataTable dt = Query(sql);
            if (dt == null || dt.Rows.Count == 0)
            {
                return "1";
            }
            else
            {
                return dt.Rows[0][0].ToString() + "-" + dt.Rows[0][1].ToString();//在位置 0 处没有任何行。
            }
        }

        public DataTable GetAllInfo(string whr)
        {
            string sql = string.Format(@"select t.module_id,
       t.module_name,
       t.module_path,
       CASE t.MODULE_STATE
         WHEN '0' THEN
          '禁用'
         WHEN '1' THEN
          '使用'
       END as MODULE_STATE,
       t.MODULE_PARENTID,
       t.MODULE_ORDERNUM1,
       t.MODULE_ORDERNUM2
  from sys_module t
 where N_ISDEL = 1 " + whr + @"
 order by t.MODULE_ORDERNUM1, t.MODULE_ORDERNUM2 ");
            //  DataTable dt = DbHelperSQL.GetDataTable(sql);

            DataTable dt = Query(sql);

            return dt;
        }

        public DataTable findParId(string roleId, string parId)
        {
            string sql = @"SELECT * FROM (SELECT min(Sys_Module.MODULE_ID) as MODULE_ID, MODULE_NAME, MODULE_PATH, MODULE_PARENTID, Sys_Module.MODULE_ORDERNUM1, MODULE_QUEUE, Sys_Module.MODULE_ORDERNUM2 FROM THKQS.Sys_RoleModule, THKQS.Sys_Module, THKQS.Sys_Role WHERE Sys_RoleModule.MODULE_ID = Sys_Module.MODULE_ID AND Sys_RoleModule.ROLE_ID IN (" + roleId + @") AND Sys_Module.MODULE_STATE = 1 AND Sys_Module.MODULE_PARENTID=" + parId + @" AND Sys_Role.ROLE_STATE=1 AND Sys_Role.ROLE_ID=Sys_RoleModule.ROLE_ID GROUP BY MODULE_QUEUE, MODULE_NAME, MODULE_PATH, MODULE_PARENTID, MODULE_ORDERNUM1, MODULE_ORDERNUM2)t ORDER BY MODULE_QUEUE, MODULE_ORDERNUM1, MODULE_ORDERNUM2 ";
            return Query(sql);
        }

        public DataTable GetAllInfo1()
        {
            string sql = string.Format(@"SELECT
    a.MODULE_ID,
    a.MODULE_NAME,
    a.MODULE_PATH,
    CASE a.MODULE_STATE
    WHEN '0' THEN
    '禁用'
    WHEN '1' THEN
    '使用'
    END as MODULE_STATE,
    a.MODULE_PARENTID,
    a.MODULE_ORDERNUM1,
    a.MODULE_ORDERNUM2
FROM
	Sys_Module a
WHERE
    a.MODULE_STATE = 1
    and a.n_isdel = 1
    AND a.MODULE_PARENTID<0
ORDER BY a.MODULE_PARENTID DESC,a.MODULE_ORDERNUM1 ASC");
            DataTable dt = Query(sql);
            return dt;
        }

        public DataTable GetAllInfo2()
        {
            string sql = string.Format(@"SELECT
    b.MODULE_ID,
    b.MODULE_NAME,
    b.MODULE_PATH,
    CASE b.MODULE_STATE
    WHEN '0' THEN
    '禁用'
    WHEN '1' THEN
    '使用'
    END as MODULE_STATE,
    b.MODULE_PARENTID,
    b.MODULE_ORDERNUM1,
    b.MODULE_ORDERNUM2
FROM
	Sys_Module a,
	Sys_Module b
WHERE
    a.MODULE_ID=b.MODULE_PARENTID
    AND a.MODULE_STATE = 1
    AND b.MODULE_STATE = 1
    and a.n_isdel = 1
    and b.n_isdel = 1
ORDER BY a.MODULE_PARENTID DESC,a.MODULE_ORDERNUM1 ASC,b.MODULE_ORDERNUM2 ASC");
            DataTable dt = Query(sql);
            return dt;
        }

        public Sys_Module GetInfoById(string id)
        {
            string sql = string.Format(@"SELECT
    sysm.MODULE_ID,
    sysm.MODULE_NAME,
    sysm.MODULE_PATH,
    sysm.MODULE_STATE,
    sysm.MODULE_PARENTID,
    sysm.MODULE_ORDERNUM1,
    sysm.MODULE_ORDERNUM2
FROM
    Sys_Module sysm
WHERE
    sysm.MODULE_ID=" + id);

            DataTable dt = Query(sql);
            Sys_Module model = new Sys_Module();

            if (dt != null && dt.Rows.Count > 0)
            {
                model.MODULE_ID = decimal.Parse(dt.Rows[0]["MODULE_ID"].ToString());
                model.MODULE_NAME = dt.Rows[0]["MODULE_NAME"].ToString();
                model.MODULE_PATH = dt.Rows[0]["MODULE_PATH"].ToString();
                model.MODULE_STATE = dt.Rows[0]["MODULE_STATE"].ToString();
                model.MODULE_PARENTID = decimal.Parse(dt.Rows[0]["MODULE_PARENTID"].ToString());
                model.MODULE_ORDERNUM1 = decimal.Parse(dt.Rows[0]["MODULE_ORDERNUM1"].ToString());
                model.MODULE_ORDERNUM2 = decimal.Parse(dt.Rows[0]["MODULE_ORDERNUM2"].ToString());
            }
            return model;
        }

        public DataTable GetModuleByAll(string win, string logonName)
        {
            string sql = @"SELECT T2.MODULE_ID AS D0,
       T2.MODULE_NAME AS D1,
       2 AS D2,
       T1.MODULE_ID AS D3,
       T1.MODULE_NAME AS D4,
       1 AS D5,
       T1.MODULE_PARENTID AS D6,
       '' AS D7,
       0 AS D8,
       T2.MODULE_PATH AS D9
  FROM SYS_MODULE T1
 INNER JOIN SYS_MODULE T2
    ON T1.MODULE_ID = T2.MODULE_PARENTID
 WHERE T2.MODULE_STATE = 1
   AND T1.MODULE_STATE = 1
   AND T1.MODULE_PARENTID = -1
 ORDER BY T1.MODULE_PARENTID  DESC,
          T1.MODULE_ORDERNUM1 ASC,
          T2.MODULE_ORDERNUM2 ASC ";

            DataTable dt = Query(sql);

            return dt;
        }

        #region 根据用户角色权限生成功能列表

        public DataTable GetModuleByRoleId1(string roleId)
        {
            string sql = @"SELECT * FROM (SELECT
    min(Sys_Module.MODULE_ID) as MODULE_ID,
    MODULE_NAME,
    MODULE_PATH,
    MODULE_PARENTID,
    Sys_Module.MODULE_ORDERNUM1,
    Sys_Module.MODULE_ORDERNUM2,
    MODULE_QUEUE
FROM
    THKQS.Sys_RoleModule,
    THKQS.Sys_Module,
    THKQS.Sys_Role
WHERE
    Sys_RoleModule.MODULE_ID = Sys_Module.MODULE_ID
    AND Sys_RoleModule.ROLE_ID IN (" + roleId + @")
    AND Sys_Module.MODULE_STATE = 1
    AND Sys_Role.ROLE_STATE=1
    AND Sys_Role.ROLE_ID=Sys_RoleModule.ROLE_ID
GROUP BY MODULE_NAME, MODULE_PATH, MODULE_PARENTID,MODULE_ORDERNUM1,MODULE_ORDERNUM2,MODULE_QUEUE)t
ORDER BY t.MODULE_QUEUE ASC,t.MODULE_ORDERNUM1 ASC, t.MODULE_ORDERNUM2 ASC";
            return Query(sql);
        }

        public DataTable GetModuleByRoleId2(string roleId)
        {
            string sql = @"SELECT
    b.MODULE_ID d0,
    b.MODULE_NAME d1,
    '2' d2,
    a.MODULE_ID d3,
    a.MODULE_NAME d4,
    '1' d5,
    a.MODULE_PARENTID d6,
    ''  d7,
    '0' d8,
    b.MODULE_PATH d9
FROM
    THKQS.SYS_MODULE a,
    THKQS.SYS_MODULE b
WHERE
    a.MODULE_ID=b.MODULE_PARENTID
    AND a.MODULE_STATE = 1
    AND b.MODULE_STATE = 1
    AND b.MODULE_ID IN (SELECT MODULE_ID FROM THKQS.Sys_RoleModule WHERE ROLE_ID IN(SELECT ROLE_ID FROM THKQS.Sys_Role WHERE Sys_Role.ROLE_STATE=1 AND Sys_RoleModule.ROLE_ID IN (" + roleId + @")))
ORDER BY a.MODULE_PARENTID DESC,a.MODULE_ORDERNUM1 ASC,b.MODULE_ORDERNUM2 ASC";
            return Query(sql);
        }

        #endregion 根据用户角色权限生成功能列表

        #region 当上一级节点发生改变后，后面的排序号往前减一

        public DataTable findNum(string num1, string num2)
        {
            string sql = "SELECT s.MODULE_ID, s.MODULE_ORDERNUM1,s.MODULE_ORDERNUM2 FROM THKQS.Sys_Module s WHERE s.MODULE_ORDERNUM1=" + num1 + "  AND s.MODULE_ORDERNUM2 BETWEEN " + num2 + " AND (SELECT max(t.MODULE_ORDERNUM2) FROM THKQS.Sys_Module t WHERE t.MODULE_ORDERNUM1=" + num1 + ") ORDER BY s.MODULE_ORDERNUM1,s.MODULE_ORDERNUM2";
            return Query(sql);
        }

        public void UPDATE(string num1, string num2, string ids, string operName)
        {
            string sql = "UPDATE SYS_MODULE SET MODULE_ORDERNUM1=" + num1 + ", MODULE_ORDERNUM2=" + num2 + " WHERE MODULE_ID=" + ids;
            NoQuery(sql, operName);
        }

        #endregion 当上一级节点发生改变后，后面的排序号往前减一

        public int INSERT(Sys_Module model, string operName)
        {
            model.MODULE_ID = GetSequence("MODULE_ID");
            model.N_ISDEL = 1;
            //替换
            string sql = string.Format(@"INSERT INTO SYS_MODULE(
MODULE_ID,
MODULE_NAME,
MODULE_PATH,
MODULE_STATE,
MODULE_PARENTID,
MODULE_ORDERNUM1,
MODULE_ORDERNUM2,
N_ISDEL)
VALUES(module_id.nextval, '{0}', '{1}', '{2}', {3}, {4},{5},{6})
", model.MODULE_NAME, model.MODULE_PATH, model.MODULE_STATE, model.MODULE_PARENTID, model.MODULE_ORDERNUM1, model.MODULE_ORDERNUM2, model.N_ISDEL);

            int count = NoQuery(sql, operName);
            return count;
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        /// <param name="operName"></param>
        /// <returns></returns>
        public int UPDATE(Sys_Module model, string operName)
        {
            string sql = string.Format(@"UPDATE SYS_MODULE SET
MODULE_NAME='{0}',
MODULE_PATH='{1}',
MODULE_STATE='{2}',
MODULE_PARENTID={3}
WHERE MODULE_ID={4}
", model.MODULE_NAME, model.MODULE_PATH, model.MODULE_STATE, model.MODULE_PARENTID, model.MODULE_ID);

            int count = NoQuery(sql, operName);
            return count;
        }

        /// <summary>
        /// 修改第一级顺序
        /// </summary>
        /// <param name="mid"></param>
        /// <param name="order"></param>
        /// <param name="operName"></param>
        /// <returns></returns>
        public int UpdateByOrd(string mid, int order, string operName)
        {
            string sql = @"update sys_module t set t.module_ordernum1=" + order + @" where  t.module_id=" + mid + @";
           update sys_module t set t.module_ordernum1=" + order + @" where t.module_parentid=" + mid;
            int count = NoQuery(sql, operName);// base.ThkService.SQLSExcute(sql, "修改序号", "UpdateByOrd", operName, operName);
                                               //  int count = NoQuery(sql, operName);
            return count;
        }

        public int UpdateByOrd2(string mid, int order, string operName)
        {
            string sql = @"  update sys_module t set t.module_ordernum2=" + order + @" where t.module_id=" + mid;

            int count = NoQuery(sql, operName);
            return count;
        }

        public int DELETE(string ids, string operName)
        {
            if (string.IsNullOrEmpty(ids))
            {
                return 0;
            }

            var sql = string.Format("update sys_module t set t.n_isdel=0,t.module_state=0 where t.module_id in({0})", ids);
            int count = NoQuery(sql, operName);
            return count;
        }

        public DataTable GetFatherModules(string parId)
        {
            string sql = @" SELECT * FROM SYS_MODULE WHERE MODULE_PARENTID=" + parId + " order by  MODULE_ORDERNUM1,module_ordernum2 ";
            var dt = Query(sql);
            return dt;
        }

        public DataTable GetFatherIdByName(string name)
        {
            string sql = String.Format(@"SELECT MODULE_PARENTID FROM SYS_MODULE WHERE Sys_Module.MODULE_NAME = '{0}'", name);
            return Query(sql);
        }

        public DataTable GetFatherNameById(int id)
        {
            string sql = String.Format(@"SELECT MODULE_NAME FROM SYS_MODULE WHERE MODULE_PARENTID ='{0}'", id);
            return Query(sql);
        }

        public DataTable GetModuleBind()
        {
            string sql = @"select *
  from (select t.module_id 模块编号,
               t.module_name 模块名称,
               t.module_path 模块路径,
               nvl((select m.module_name
                     from sys_module m
                    where m.module_id = t.module_parentid),
                   t.module_name) 上级模块,
               --  t.module_state --
               decode(t.MODULE_STATE, '1', '使用', '禁用') 模块状态
          from sys_module t)
 order by 上级模块, 模块编号 ";

            DataTable dt = Query(sql);

            return dt;
        }

        public int GetRecordCount(string whereStr, string parId)
        {
            StringBuilder strSql = new StringBuilder();
            //strSql.Append(@"SELECT count(1) FROM THKQS.Sys_Module sysm WHERE 1=1 AND (sysm.MODULE_PARENTID = " + parId + @" OR sysm.MODULE_PARENTID IN (SELECT MODULE_ID FROM THKQS.Sys_Module WHERE MODULE_PARENTID=" + parId + @"))");
            strSql.Append(@"SELECT count(1)
  FROM (SELECT ROW_NUMBER() OVER(ORDER BY MODULE_ID asc) as r,
               t.MODULE_ID,
               (select p.module_name
                  from Sys_Module p
                 where p.module_id = t.module_parentid) as MODULE_PARENTID,
               t.MODULE_NAME,
               t.MODULE_PATH,
               CASE t.MODULE_STATE
                 WHEN '0' THEN
                  '禁用'
                 WHEN '1' THEN
                  '正常'
               END as MODULE_STATE
          FROM Sys_Module t
         WHERE 1 = 1
           and t.n_isdel = 1 ");
            if (!string.IsNullOrEmpty(whereStr))
            {
                strSql.Append(whereStr);
            }
            strSql.Append(@" ) TT");
            return GetCount(strSql.ToString());
        }

        public DataTable GetListByPage(string whereStr, string parId, string orderby, int startIndex, int endIndex)
        {
            string sql = string.Format(@"SELECT * FROM (SELECT  ROW_NUMBER() OVER(ORDER BY " + orderby + @") as r,
   t.MODULE_ID,
       (select p.module_name
          from Sys_Module p
         where p.module_id = t.module_parentid) as MODULE_PARENTID,
       t.MODULE_NAME,
       t.MODULE_PATH,
       CASE t.MODULE_STATE
         WHEN '0' THEN
          '禁用'
         WHEN '1' THEN
          '正常'
       END as MODULE_STATE
  FROM Sys_Module t
 WHERE 1 = 1
   and t.n_isdel = 1 ");

            if (!string.IsNullOrEmpty(whereStr))
            {
                sql += whereStr;
            }

            sql += @" ) TT";
            if (startIndex >= 2)
            {
                sql += " WHERE TT.r BETWEEN (" + startIndex + "-1)*" + endIndex + "+1 AND " + startIndex + "*" + endIndex + "";
            }
            else
            {
                sql += " WHERE TT.r BETWEEN (" + startIndex + "-1)*" + endIndex + " AND " + startIndex + "*" + endIndex + "";
            }

            return Query(sql);
        }

        public List<Sys_Module> GetAll
        {
            get
            {
                var obj = HttpRuntimeCache.Get("Sys_Module");
                List<Sys_Module> data;
                if (obj == null)
                {
                    var sql = "SELECT MODULE_ID,MODULE_NAME,MODULE_PATH,MODULE_STATE,MODULE_PARENTID,MODULE_ORDERNUM1,MODULE_ORDERNUM2 FROM SYS_MODULE";
                    var dt = Query(sql);
                    data = new List<Sys_Module>();
                    foreach (DataRow item in dt.Rows)
                    {
                        var entity = new Sys_Module
                        {
                            MODULE_ID = Convert.ToDecimal(item["MODULE_ID"]),
                            MODULE_NAME = Convert.ToString(item["MODULE_NAME"]),
                            MODULE_PATH = Convert.ToString(item["MODULE_PATH"]),
                            MODULE_STATE = Convert.ToString(item["MODULE_STATE"]),
                            MODULE_PARENTID = Convert.ToDecimal(item["MODULE_PARENTID"]),
                            MODULE_ORDERNUM1 = Convert.ToDecimal(item["MODULE_ORDERNUM1"]),
                            MODULE_ORDERNUM2 = item["MODULE_ORDERNUM2"] == DBNull.Value ? 0 : Convert.ToDecimal(item["MODULE_ORDERNUM2"])
                        };
                        data.Add(entity);
                    }
                    HttpRuntimeCache.Set("Sys_Module", data, 60, false);
                }
                else
                {
                    data = (List<Sys_Module>)obj;
                }
                return data;
            }
        }
    }
}